package com.example.edupublisher.mapper;

import com.example.edupublisher.bean.ExamDurationStats;
import com.example.edupublisher.bean.ExamStats;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface ExamMapper {

    @Select("SELECT \n" +
            "\tSUM(exam_taken_count) examCount,\n" +
            "\tSUM(exam_total_score) / SUM(exam_taken_count) avgScore,\n" +
            "\tSUM(exam_total_during_sec) / SUM(exam_taken_count) avgTime\n" +
            "FROM dws_examination_paper_exam_window\n" +
            "WHERE DATE_FORMAT(cur_date,\"yyyyMMdd\") = (#{date})\n" +
            "GROUP BY paper_id\n" +
            "ORDER BY avgScore DESC")
   List<ExamStats> selectCourseExam(@Param("date") Integer date);




    @Select("SELECT \n" +
            "score_duration scoreDuration,\n" +
            "SUM(user_count) userCount\n" +
            "FROM\n" +
            "dws_examination_paper_score_duration_exam_window\n" +
            "WHERE DATE_FORMAT(cur_date,\"yyyyMMdd\") = (#{date})\n" +
            "GROUP BY score_duration")
    List<ExamDurationStats> selectDurationStats(@Param("date") Integer date);
}
